﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// SQLHelper 的摘要说明
/// </summary>
public class SQLHelper
{
    private static string sqlconn = System.Configuration.ConfigurationManager.AppSettings["sqlcon"].ToString().Trim();
    public SQLHelper()
    {

        //
        // TODO: 在此处添加构造函数逻辑
        //
    }
    public DataSet Bindgong()
    {
        string sql = "select * from G_TABLE";
        users us = new users();
        DataSet ds = us.GetDs(sql);
        return ds;
    }
    public DataSet Binduser()
    {
        string sql = "select * from T_TABLE";
        users us = new users();
        DataSet ds = us.GetDs(sql);
        return ds;
    }
    public DataSet Bindyao()
    {
        string sql = "select * from Y_TABLE";
        users us = new users();
        DataSet ds = us.GetDs(sql);
        return ds;
    }

    protected SqlConnection GetConn()
    {
        SqlConnection conn = new SqlConnection(sqlconn);
        return conn;
    }

    ///command对象的executenonquery方法
    protected int ExeNnQuery(string sql)
    {
        SqlConnection conn = GetConn();
        try
        {
            conn.Open();

            SqlCommand comm = new SqlCommand(sql, conn);
            return comm.ExecuteNonQuery();

        }

        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }
    protected int ExeScale(string sql)
    {
        SqlConnection conn = GetConn();
        try
        {
            conn.Open();

            SqlCommand comm = new SqlCommand(sql, conn);
            return (int)comm.ExecuteScalar();


        }

        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }
    protected int ExecuteScalar(string sql)
    {
        SqlConnection coon = GetConn();
        try
        {
            coon.Open();
            SqlCommand comn = new SqlCommand(sql, coon);
            return (int)comn.ExecuteScalar();

        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            coon.Close();
        };
    }
    protected SqlDataReader EexReader(string sql)
    {
        SqlConnection conn = GetConn();
        try
        {
            conn.Open();

            SqlCommand comm = new SqlCommand(sql, conn);
            return comm.ExecuteReader();


        }

        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }

    }
    protected SqlDataReader ExecuteReader(string sql)
    {
        SqlConnection coon = GetConn();
        try
        {

            coon.Open();

            SqlCommand comn = new SqlCommand(sql, coon);
            return comn.ExecuteReader();

        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
    }
    
    protected DataSet GetDs(string sql)
    {
        SqlConnection conn = GetConn();
        try
        {
            SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            return ds;
        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }
}